<?php

namespace app\admin\service\setting;

use think\facade\Db;
use tmcore\base\BaseService;
use tmcore\exceptions\ValidateException;
use tmcore\utils\Helper;

class DbMysqlService extends BaseService
{
    /**
     * 获取表状态分页列表
     * @param $params
     * @return array
     */
    public static function getList($params)
    {
        if (env('database.type') !== 'mysql') {
            return [];
        }

        $sql = "SHOW TABLE STATUS WHERE name NOT LIKE '%migrations'";
        if(!empty($params['table'])) {
            if(!Helper::remove_xss($params['table'])) throw new ValidateException('上传参数有sql注入风险');
            $sql .= " and name = '{$params['table']}'";
        }

        $list = Db::query(Db::raw($sql)->getValue());
        $pageData = self::getPageData();
        return page_array($list, $pageData['limit'], $pageData['page']);
    }

    /**
     * 获取表字段
     * @param string $table
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function detail(string $table)
    {
        if ($table && env('database.type') === 'mysql') {
            // 从数据库中获取表字段信息
            $sql = "SELECT * FROM `information_schema`.`columns` WHERE table_schema ='".env('database.database')."' AND table_name ='".$table."' ORDER BY ORDINAL_POSITION";
            // 加载主表的列
            $columnList = [];
            foreach (Db::query($sql) as $column) {
                $columnList[] = [
                    'column_key' => $column['COLUMN_KEY'],
                    'column_name' => $column['COLUMN_NAME'],
                    'data_type' => $column['DATA_TYPE'],
                    'column_comment' => $column['COLUMN_COMMENT'],
                    'extra' => $column['EXTRA'],
                    'column_type' => $column['COLUMN_TYPE'],
                    'is_nullable' => $column['IS_NULLABLE'],
                ];
            }
            return $columnList;
        }
        return [];
    }

    /**
     * 优化表
     * @param array $tables
     * @return bool
     */
    public static function optimize(array $tables)
    {
        foreach ($tables as $table) {
            if (env('database.type') === 'mysql') {
                Db::query("optimize table `{$table}`");
            } else {
                Db::query("VACUUM table `{$table}`");
            }
        }
        return true;
    }

    /**
     * 清理表碎片
     * @param array $tables
     * @return bool
     */
    public static function fragment(array $tables): bool
    {
        foreach ($tables as $table) {
            if (env('database.type') === 'mysql') {
                Db::query("analyze table `{$table}`");
            } else {
                Db::query("analyze table `{$table}`");
            }
        }
        return true;
    }
}